'''
Author: your name
Date: 2021-11-23 16:29:44
LastEditTime: 2021-11-24 14:38:34
LastEditors: Please set LastEditors
Description: 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
FilePath: \Excel\demo4_zhlc.py
'''
from os import linesep
from typing import ItemsView, Literal
import xlrd
import pymysql
# 打开数据所在的工作簿，以及选择存有数据的工作表
file_name = r'zhlc.xls'
book = xlrd.open_workbook(file_name,formatting_info=True)
get_sheet_name = book.sheet_names()[0]
sheet = book.sheet_by_name(get_sheet_name)
# 建立一个MySQL连接
""" conn = pymysql.connect(
        host='localhost', 
        user='root', 
        passwd='root',  
        db='python',  
        port=3306,  
        charset='utf8') """
# 获得游标
rowss = 0
# 获取列数
r_num = sheet.nrows
# 获取行数
c_num = sheet.ncols
merge = sheet.merged_cells
read_data =[]
for r in range(2,r_num):
    li = []
    for c in range(c_num):
    	# 读取每个单元格里的数据，合并单元格只有单元格内的第一行第一列有数据，其余空间都为空
        cell_value = sheet.row_values(r)[c]
        # 判断空数据是否在合并单元格的坐标中，如果在就把数据填充进去
        if cell_value is None or cell_value == '':
            for (rlow, rhigh, clow, chigh) in merge:
                if rlow <= r < rhigh:
                    if clow <= c < chigh:
                        cell_value = sheet.cell_value(rlow, clow)
        li.append(cell_value)
    read_data.append(li)
# print(read_data)
i = 0
for index in read_data:
    GUID  = index[0]
    Name  = index[1]
    SubSectionGUID  = index[2]
    SubSectionName  = index[3]
    SubentryGUID  = index[4]
    SubentryName  = index[5]
    ItemsGUID = index[6]
    ItemsName = index[7]
    values = (GUID,Name,SubSectionGUID,SubSectionName,SubentryGUID,SubentryName,ItemsGUID,ItemsName)
    i= i+1
    print(values)
print(i)

